# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: http://doc.scrapy.org/en/latest/topics/item-pipeline.html
import json
import codecs
import MySQLdb
import MySQLdb.cursors
from scrapy.pipelines.images import ImagesPipeline
# from SearchSpider import settings
from twisted.enterprise import adbapi


class SearchspiderPipeline(object):
    def process_item(self, item, spider):
        return item


# 下载图片
class SearchImagePipeline(ImagesPipeline):
    # 重写该方法可从result中获取到图片的实际下载地址
    def item_completed(self, results, item, info):
        for ok, value in results:
            image_file_path = value["path"]
        item['images_path'] = image_file_path
        return item


# 保存到json
class JsonWithEncodingPipeline(object):
    def __init__(self):
        self.file = codecs.open('data.json', 'w', encoding='utf-8')

    def process_item(self, item, spider):
        lines = json.dumps(dict(item), ensure_ascii=False) + '\n'
        self.file.write(lines)
        return item

    def spider_closed(self, spider):
        self.file.close()


# 保存到mysql(同步)
class MysqlPipeline(object):
    def __init__(self, settings):
        self.conn = MySQLdb.connect(host=settings.MYSQL_HOST, user=settings.MYSQL_USER, passwd=settings.MYSQL_PASSWORD,
                                    port=settings.MYSQL_PORT, db=settings.MYSQL_DBNAME, charset='utf8')
        self.cursor = self.conn.cursor()

    def process_item(self, item, spider):
        insert_sql = """
            insert into jobbole(url_object_id, title, url, thumbnail, images_path, create_time, praise_nums, fav_nums, comment_nums, content, tags)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """
        self.cursor.execute(insert_sql, (item['url_object_id'], item['title'], item['url'], item['thumbnail'],
                                         item['images_path'], item['create_time'], item['praise_nums'],
                                         item['fav_nums'], item['comment_nums'], item['content'], item['tags']))
        self.conn.commit()


# 保存到mysql(异步)
class MysqlTwistedPipeline(object):
    def __init__(self, dbpool):
        self.dbpool = dbpool

    @classmethod
    def from_settings(cls, settings):
        dbparms = dict(
            host=settings['MYSQL_HOST'],
            db=settings['MYSQL_DBNAME'],
            user=settings['MYSQL_USER'],
            passwd=settings['MYSQL_PASSWORD'],
            port=settings['MYSQL_PORT'],
            charset='utf8',
            cursorclass=MySQLdb.cursors.DictCursor,
            use_unicode=True,
        )
        dbpool = adbapi.ConnectionPool('MySQLdb', **dbparms)
        return cls(dbpool)

    def process_item(self, item, spider):
        query = self.dbpool.runInteraction(self.do_insert, item)
        query.addErrback(self.handle_error, item, spider)

    def do_insert(self, cursor, item):
        insert_sql, params = item.get_insert_sql()
        cursor.execute(insert_sql, params)

    def handle_error(self, failure, item, spider):
        # 处理异步插入的异常
        print(failure)

    def do_insert(self, cursor, item):
        insert_sql = """
            insert into jobbole(url_object_id, title, url, thumbnail, images_path, create_time, praise_nums, fav_nums, comment_nums, content, tags)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """
        cursor.execute(insert_sql, (item['url_object_id'], item['title'], item['url'], item['thumbnail'],
                                    item['images_path'], item['create_time'], item['praise_nums'],
                                    item['fav_nums'], item['comment_nums'], item['content'], item['tags']))